﻿using System;
using System.Collections.Generic;
using System.Data.SQLite;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using QQTools.Data.Models;

namespace QQTools.Data.Repositories.BlockGroup
{
    public class BlockGroupQQRepository
    {
        private readonly string _connectionString;

        public BlockGroupQQRepository(string connectionString)
        {
            _connectionString = connectionString;
        }

        /// <summary>
        /// 新增数据（异步）
        /// </summary>
        public async Task AddAsync(BlockGroupQQModel blockGroupQQ)
        {
            try
            {
                using (var connection = new SQLiteConnection(_connectionString))
                {
                    await connection.OpenAsync();
                    using (var command = new SQLiteCommand(@"
                    INSERT INTO block_group_qq (qq, block_number, create_time, update_time)
                    VALUES (@QQ, @BlockNumber, @CreateTime, @UpdateTime);", connection))
                    {
                        command.Parameters.AddWithValue("@QQ", blockGroupQQ.QQ);
                        command.Parameters.AddWithValue("@BlockNumber", blockGroupQQ.BlockNumber);
                        command.Parameters.AddWithValue("@CreateTime", blockGroupQQ.CreateTime);
                        command.Parameters.AddWithValue("@UpdateTime", blockGroupQQ.UpdateTime);

                        await command.ExecuteNonQueryAsync();
                    }
                }

            }
            catch (Exception ex)
            {
                throw new ApplicationException("新增数据失败", ex);
            }
        }

        /// <summary>
        /// 删除数据（异步）
        /// </summary>
        public async Task DeleteAsync(int id)
        {
            try
            {
                using (var connection = new SQLiteConnection(_connectionString))
                {
                    await connection.OpenAsync();
                    var command = new SQLiteCommand("DELETE FROM block_group_qq WHERE id = @Id", connection);
                    command.Parameters.AddWithValue("@Id", id);

                    await command.ExecuteNonQueryAsync();
                }
            }
            catch (Exception ex)
            {
                throw new ApplicationException("删除数据失败", ex);
            }
        }

        /// <summary>
        /// 根据 ID 查询数据（异步）
        /// </summary>
        public async Task<BlockGroupQQModel> GetByIdAsync(int id)
        {
            try
            {
                using (var connection = new SQLiteConnection(_connectionString))
                {
                    await connection.OpenAsync();
                    var command = new SQLiteCommand("SELECT * FROM block_group_qq WHERE id = @Id ORDER BY id ASC", connection);
                    command.Parameters.AddWithValue("@Id", id);

                    using (var reader = await command.ExecuteReaderAsync())
                    {
                        if (await reader.ReadAsync())
                        {
                            return MapToBlockGroupQQ(reader);
                        }
                    }
                }
                return null;
            }
            catch (Exception ex)
            {
                throw new ApplicationException("查询数据失败", ex);
            }
        }

        /// <summary>
        /// 查询所有数据（异步）
        /// </summary>
        public async Task<List<BlockGroupQQModel>> GetAllAsync()
        {
            var blockGroupQQs = new List<BlockGroupQQModel>();
            try
            {
                using (var connection = new SQLiteConnection(_connectionString))
                {
                    await connection.OpenAsync();
                    var command = new SQLiteCommand("SELECT * FROM block_group_qq ORDER BY id ASC", connection);

                    using (var reader = await command.ExecuteReaderAsync())
                    {
                        while (await reader.ReadAsync())
                        {
                            blockGroupQQs.Add(MapToBlockGroupQQ(reader));
                        }
                    }
                }
                return blockGroupQQs;
            }
            catch (Exception ex)
            {
                throw new ApplicationException("查询所有数据失败", ex);
            }
        }

        /// <summary>
        /// 清空表（异步）
        /// </summary>
        public async Task ClearTableAsync()
        {
            try
            {

                using (var connection = new SQLiteConnection(_connectionString))
                {
                    await connection.OpenAsync();
                    using (var command = new SQLiteCommand("DELETE FROM block_group_qq;", connection))
                    {
                        await command.ExecuteNonQueryAsync();
                    }
                }

            }
            catch (Exception ex)
            {
                throw new ApplicationException("清空表失败", ex);
            }
        }


        /// <summary>
        /// 映射 SqlDataReader 到 BlockGroupQQ 对象
        /// </summary>
        private BlockGroupQQModel MapToBlockGroupQQ(System.Data.Common.DbDataReader reader)
        {
            return new BlockGroupQQModel
            {
                Id = reader.GetInt32(reader.GetOrdinal("id")),
                QQ = reader.GetString(reader.GetOrdinal("qq")),
                BlockNumber = reader.GetInt32(reader.GetOrdinal("block_number")),
                CreateTime = reader.GetDateTime(reader.GetOrdinal("create_time")),
                UpdateTime = reader.GetDateTime(reader.GetOrdinal("update_time"))
            };
        }

        public async Task UpdateAsync(BlockGroupQQModel blockGroupQQ)
        {
            using (var connection = new SQLiteConnection(_connectionString))
            {
                await connection.OpenAsync();
                using (var command = new SQLiteCommand(@"
                    UPDATE block_group_qq
                    SET qq = @QQ, block_number = @BlockNumber, update_time = CURRENT_TIMESTAMP
                    WHERE id = @Id;", connection))
                {
                    command.Parameters.AddWithValue("@Id", blockGroupQQ.Id);
                    command.Parameters.AddWithValue("@QQ", blockGroupQQ.QQ);
                    command.Parameters.AddWithValue("@BlockNumber", blockGroupQQ.BlockNumber);

                    await command.ExecuteNonQueryAsync();
                }
            }
        }


        /// <summary>
        /// 插入或更新 BlockGroupQQ 记录（异步）
        /// </summary>
        /// <param name="blockGroupQQ">BlockGroupQQ 实体对象</param>
        /// <returns>返回操作是否成功</returns>
        public async Task AddOrUpdateAsync(BlockGroupQQModel blockGroupQQ)
        {
            using (var connection = new SQLiteConnection(_connectionString))
            {
                await connection.OpenAsync();
                // 检查 qq 是否已经存在
                using (var checkCommand = new SQLiteCommand("SELECT id FROM block_group_qq WHERE qq = @QQ", connection))
                {
                    checkCommand.Parameters.AddWithValue("@QQ", blockGroupQQ.QQ);
                    var existingId = await checkCommand.ExecuteScalarAsync();

                    if (existingId != null)
                    {
                        // 如果存在，则更新数据
                        int id = Convert.ToInt32(existingId);
                        blockGroupQQ.Id = id;
                        await UpdateAsync(blockGroupQQ);
                    }
                    else
                    {
                        // 如果不存在，则插入新数据
                        await AddAsync(blockGroupQQ);
                    }
                }
            }
        }


        /// <summary>
        /// 根据 QQ 让 block_number 自增 1（异步）
        /// </summary>
        public async Task IncrementBlockNumberAsync(string qq)
        {
            try
            {
                using (var connection = new SQLiteConnection(_connectionString))
                {
                    await connection.OpenAsync();
                    var command = new SQLiteCommand(
                        "UPDATE block_group_qq SET block_number = block_number + 1, update_time = @UpdateTime " +
                        "WHERE qq = @QQ", connection);
                    command.Parameters.AddWithValue("@UpdateTime", DateTime.Now);
                    command.Parameters.AddWithValue("@QQ", qq);

                    await command.ExecuteNonQueryAsync();
                }
            }
            catch (Exception ex)
            {
                throw new ApplicationException("自增 block_number 失败", ex);
            }
        }

    }
}